Powershell script to check SQL service status and send email
In windows server we can create a PowerShell script to trigger an email on the basis of SQL service status.
Solution Below
The solution for checking the status of SQL Server service can be achieved by using the “Get-Service” cmdlet. In the below example we are using some common naming conventions of SQL server like MSSQL$, MSSQLSERVER, and SQL Server, etc.
below code is to get the server status. Note: please check your SQL server name and replace them “MSSQLSERVER” in below code
$server = $env:computername
$object = Get-service -ComputerName $server | where {($_.name -like "MSSQL$*" -or $_.name -like "MSSQLSERVER" -or $_.name -like "SQL Server (*") }
if ($object){
$instDetails= $object |select Name,Status
$instDetails
}else{
Write-Host "0 SQL Server instances discovered"
}
To send email we will use some SMTP server request as below.
Email solution #1 :Simple method
Send-MailMessage -To “<recipient’s email address>” -From “<sender’s email address>” -Subject “Your subject” -Body “enter plain body text”
Email solution #2 : creating a Custom SMTP Function
$Username = "-- your SMTP server username --"; //ex: if you are using gmail smtp then replace with gmail data
$Password = "-- your SMTP server password --";
function Send-ToEmail([string]$email, [string]$bodytext){
$message = new-object Net.Mail.MailMessage;
$message.From = "YourName@gmail.com";
$message.To.Add($email);
$message.Subject = "subject text here...";
$message.Body = $bodytext;
$smtp = new-object Net.Mail.SmtpClient("smtp.gmail.com", "587");
$smtp.EnableSSL = $true;
$smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
$smtp.send($message);
write-host "Mail Sent" ;
}
//use this line on validating the SQL server Status
Send-ToEmail -email "reciever@gmail.com" -bodytext "---pass status here ---";
If you have any problem solving above, comment below we will respond soon.
Arjun is a Full-stack developer, who is fond of the web. Lives in Chikmagalur, Karnataka, India
